﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Syncfusion.XlsIO;
using System.Web;
using System.Web.Security;
using System.Drawing;
using System.Data;
using System.Configuration;

namespace LDSP.Util
{
    public enum DownloadType { PromptDialog = 0, Open, Save, Unknow }
    public class ExcelClass
    {
        #region Local variable
        private ExcelEngine excelEngine = new ExcelEngine();

        #endregion

        #region Public porperty
        private IWorkbook workbook;
        public IWorkbook Workbook
        {
            get { return this.workbook; }
        }

        private IWorksheets sheets;
        public IWorksheets Sheets
        {
            get { return this.sheets; }
        }
        #endregion

        #region Constructor
        public ExcelClass(string fileName)
        {
            IApplication application = excelEngine.Excel;
            //-----------added by marco 2010-8-18----
            string extennName = fileName.Substring(fileName.LastIndexOf('.') + 1);
            if (extennName == "xlsx")
                application.DefaultVersion = ExcelVersion.Excel2010;
            else if (extennName == "xls")
                application.DefaultVersion = ExcelVersion.Excel97to2003;
            else
                application.DefaultVersion = ExcelVersion.Excel2007;
            //-----------added by marco 2010-8-18----

            this.workbook = application.Workbooks.Open(fileName);
            this.sheets = this.workbook.Worksheets;
        }
        public ExcelClass(string[] sheetNames)
        {
            IApplication application = excelEngine.Excel;
            this.workbook = application.Workbooks.Create(sheetNames);
            this.sheets = this.workbook.Worksheets;

            // Todo: add styles in here, the style can be ovrride by virtual function
            IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
            this.SetHeaderStyle(headerStyle);
            IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
            this.SetBodyStyle(bodyStyle);
            IStyle textStyle = workbook.Styles.Add("TextStyle");
            this.SetTextStyle(textStyle);
        }
        #endregion

        #region Sheet of excel operate
        public IWorksheet GetSheetByName(string sheetName)
        {
            if (string.IsNullOrEmpty(sheetName))
                return this.workbook.Worksheets[0];
            return this.workbook.Worksheets[sheetName];
        }
        public IWorksheet GetSheetByIndex(int index)
        {
            if (index > this.workbook.Worksheets.Count || index < 0)
                return this.workbook.Worksheets[0];
            return this.workbook.Worksheets[index];
        }

        public IWorksheet GenerateSheet(string name)
        {
            IWorksheet sheet = this.workbook.Worksheets.AddCopy(0);
            sheet.Name = name;
            return sheet;
        }

        public void RemoveSheet(int index)
        {
            this.workbook.Worksheets.Remove(index);
        }
        #endregion

        #region To Excel
        public void ToExcel(string fileName, DownloadType downloadType)
        {
            switch (downloadType)
            {
                case DownloadType.PromptDialog:
                    this.workbook.SaveAs(fileName, ExcelSaveType.SaveAsXLS, HttpContext.Current.Response, ExcelDownloadType.PromptDialog);
                    break;
                case DownloadType.Open:
                    this.workbook.SaveAs(fileName, ExcelSaveType.SaveAsXLS, HttpContext.Current.Response, ExcelDownloadType.Open);
                    break;
                default:
                    workbook.SaveAs(fileName);
                    break;
            }
            //No exception will be thrown if there are unsaved workbooks.
            excelEngine.ThrowNotSavedOnDestroy = false;
            excelEngine.Dispose();
        }
        #endregion

        #region To Data Table
        public DataTable ToDataTable()
        {
            return this.ToDataTable(0, 1, 1);
        }
        public DataTable ToDataTable(int sheetIndex)
        {
            return this.ToDataTable(sheetIndex, 1, 1);
        }
        public DataTable ToDataTable(int sheetIndex, int tableTop, int tableLeft)
        {
            DataTable dt = new DataTable();

            // constructor data table column
            List<int> columnIndexList = new List<int>();
            for (int i = tableLeft; i < 256; i++)
            {
                string columnName = this.sheets[sheetIndex].Range[tableTop, i].Text;
                if (string.IsNullOrEmpty(columnName))
                    break;

                dt.Columns.Add(columnName);
                columnIndexList.Add(i);
            }

            // add data in the data table
            for (int i = tableTop + 1; ; i++)
            {
                bool emptyRow = true;

                DataRow row = dt.NewRow();
                for (int j = 0; j < columnIndexList.Count; j++)
                {
                    string value = this.sheets[sheetIndex].Range[i, columnIndexList[j]].Value;
                    if (string.IsNullOrEmpty(value))
                        continue;
                    emptyRow = false;
                    row[j] = value;
                }

                if (emptyRow == true)
                    break;

                dt.Rows.InsertAt(row, 0);
            }

            return dt;
        }
        #endregion

        #region Image insert into excel
        public void ImportPicture(IWorksheet sheet, System.Drawing.Image img, int top)
        {
            this.ImportPicture(sheet, img, top, 1);
        }
        public void ImportPicture(IWorksheet sheet, System.Drawing.Image img, int top, int left)
        {
            sheet.Pictures.AddPicture(top, left, img);
        }
        #endregion

        #region Data Table insert into the excel
        public void ImportDataTable(IWorksheet sheet, DataTable dataSource, int top, params int[] mergeColumns)
        {
            this.ImportDataTable(sheet, dataSource, top, 1, mergeColumns);
        }
        public void ImportDataTable(IWorksheet sheet, DataTable dataSource, int top, int left, params int[] mergeColumns)
        {
            this.ImportDataTable(sheet, dataSource, top, left, dataSource.Rows.Count, dataSource.Columns.Count, mergeColumns);
        }
        public void ImportDataTable(IWorksheet sheet, bool displayHeader, DataTable dataSource, int top, int left, params int[] mergeColumns)
        {
            this.ImportDataTable(sheet, displayHeader, dataSource, top, left, 65535, 255, mergeColumns);
        }

        public void ImportDataTable(IWorksheet sheet, DataTable dataSource, int top, int left, int maxRowLen, int maxColumnLen, params int[] mergeColumns)
        {
            this.ImportDataTable(sheet, true, dataSource, top, left, maxRowLen, maxColumnLen, mergeColumns);
        }


        public void ImportDataTable(IWorksheet sheet, bool displayHeader, DataTable dataSource, int top, int left, int maxRowLen, int maxColumnLen, params int[] mergeColumns)
        {
            if (dataSource == null || dataSource.Columns.Count < 1)
                return;

            // Todo: 
            sheet.ImportDataTable(dataSource, true, top, left, maxRowLen, maxColumnLen);
            if (displayHeader)
                sheet.Range[top, left, top, left + dataSource.Columns.Count - 1].CellStyleName = "HeaderStyle";
            else
            {
                int rowLen = dataSource.Rows.Count, colLen = dataSource.Columns.Count;
                for (int i = top; i < top + rowLen; i++)
                    for (int j = left; j < left + colLen; j++)
                        sheet.Range[i, j, i, j].Value = sheet.Range[i + 1, j, i + 1, j].Value;
                sheet.Range[top + rowLen, left, top + rowLen, left + colLen].Value = string.Empty;
            }
        }
        #endregion

        #region Cell operation
        public void MergeRow(IWorksheet sheet, int len, int top, int left)
        {
            for (int i = 1; i < len + 1; i++)
            {
                string context = sheet.Range[top, i + left - 1].Text;
                int mergeLen = 1;
                for (int j = i + 1; j < len + 1; j++)
                {
                    if (sheet.Range[top, j + left - 1].Text == context)
                        mergeLen++;
                    else
                        break;
                }
                if (mergeLen > 1)
                {
                    sheet.Range[top, i + left - 1, top, i + mergeLen - 1 + left - 1].Merge();
                    i = i + mergeLen - 1;
                }
            }
        }

        public void MergeColumn(IWorksheet sheet, int len, int top, int left)
        {
            for (int i = 1; i < len + 1; i++)
            {
                string context = sheet.Range[top, i + left - 1].Text;
                if (sheet.Range[top + 1, i + left - 1].Text == context)
                    sheet.Range[top, i + left - 1, top + 1, i + left - 1].Merge();
            }
        }

        // Set Column width
        public void SetColumnWidth(IWorksheet sheet, int col, double width)
        {
            sheet.Range[1, col].ColumnWidth = width;
        }
        public void SetColumnWidth(IWorksheet sheet, int top, int left, int bottom, int right, int width)
        {
            sheet.Range[top, left, bottom, right].ColumnWidth = width;
        }

        // Set Cell height
        public void SetRowHeight(IWorksheet sheet, int top, int left, double height)
        {
            sheet.Range[top, left].RowHeight = height;
        }

        /// <summary>
        /// Set font sample: 
        /// IFont font = exl.Workbook.CreateFont();
        /// font.FontName = "Arial";
        /// font.Size = 12;
        /// font.RGBColor = Color.SeaGreen;
        /// font.Bold = true;
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="font"></param>
        public void SetCellFont(IWorksheet sheet, int row, int col, IFont font)
        {
            sheet.Range[row, col].CellStyle.Font.FontName = font.FontName;
            sheet.Range[row, col].CellStyle.Font.Size = font.Size;
            sheet.Range[row, col].CellStyle.Font.Bold = font.Bold;
            sheet.Range[row, col].CellStyle.Font.Color = font.Color;
        }
        public void SetCellFont(IWorksheet sheet, int row, int col, int bottom, int right, IFont font)
        {
            sheet.Range[row, col, bottom, right].CellStyle.Font.FontName = font.FontName;
            sheet.Range[row, col, bottom, right].CellStyle.Font.Size = font.Size;
            sheet.Range[row, col, bottom, right].CellStyle.Font.Bold = font.Bold;
            sheet.Range[row, col, bottom, right].CellStyle.Font.Color = font.Color;
        }
        #endregion

        #region Text operation
        // Text
        public void SetText(IWorksheet sheet, string title, int top, int left, int bottom, int right)
        {
            sheet.Range[top, left, bottom, right].Text = title;
            sheet.Range[top, left, bottom, right].Merge();
            sheet.Range[top, left, bottom, right].CellStyleName = "TextStyle";
        }

        // Alignment settings
        public void SetTextAlignment(IWorksheet sheet, int col, ExcelHAlign align)
        {
            sheet.Range[1, col].HorizontalAlignment = align;
        }
        public void SetTextAlignment(IWorksheet sheet, int top, int left, int bottom, int right, ExcelHAlign align)
        {
            sheet.Range[top, left, bottom, right].HorizontalAlignment = align;
        }

        // Text control
        public void SetTextControl(IWorksheet sheet, int col, bool isWrapText)
        {
            sheet.Range[1, col].WrapText = isWrapText;
        }
        public void SetTextControl(IWorksheet sheet, int top, int left, int bottom, int right, bool isWrapText)
        {
            sheet.Range[top, left, bottom, right].WrapText = isWrapText;
        }
        #endregion

        #region Set style of Excel
        /// <summary>
        /// Virtual function, user can be override it 
        /// </summary>
        /// <param name="headerStyle"></param>
        public virtual void SetHeaderStyle(IStyle headerStyle)
        {
            headerStyle.Color = Color.BlueViolet;
            headerStyle.Font.Bold = true;
            headerStyle.Font.FontName = "Arial";
            headerStyle.Font.Color = ExcelKnownColors.White;
            headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
            headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
            headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
            headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
            headerStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
            headerStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;

            headerStyle.WrapText = false;
            headerStyle.ShrinkToFit = false;
        }
        /// <summary>
        /// Set body style, virtual function, user can be override it
        /// </summary>
        /// <param name="bodyStyle"></param>
        public virtual void SetBodyStyle(IStyle bodyStyle)
        {
            bodyStyle.Color = Color.FromArgb(239, 243, 247);
            bodyStyle.Font.FontName = "Arial";
            bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
            bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
            bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
            bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
            bodyStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
            bodyStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;

            //bodyStyle.WrapText = false;
            //bodyStyle.ShrinkToFit = true;
        }

        /// <summary>
        /// Set text style, virtual funcin, user can be override it 
        /// default: font: "Arial"
        /// </summary>
        /// <param name="textStyle"></param>
        public virtual void SetTextStyle(IStyle textStyle)
        {
            textStyle.Font.FontName = "Arial";

            //textStyle.WrapText = true;
            //textStyle.ShrinkToFit = true;
        }

        #endregion

    }
}
